package dimdidu.board.review;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.catalina.User;

import dimdidu.interface_class.RdbmsDAO;

public class BoardReviewDAO extends RdbmsDAO{
	private static BoardReviewDAO instance;
	public static BoardReviewDAO getInstance(){
		if(instance == null){
			instance = new BoardReviewDAO();
		}
		return instance;
	}
	
	private BoardReviewDAO(){}

	

	public int createReview(BoardReviewVO review)throws SQLException{
		Connection con= null;
		PreparedStatement pstmt=null;
		String insertQuery = "insert into boardreview values(?,?,?,?,?,?,?,?)";
		try{
			con=getConnection();
			pstmt=con.prepareStatement(insertQuery);
			pstmt.setInt(1, review.getNo());
			pstmt.setInt(2, review.getMemberNo());
			pstmt.setString(3,review.getName());
			pstmt.setString(4,review.getHit());
			pstmt.setString(5,review.getDate());
			pstmt.setString(6,review.getPoint());
			pstmt.setString(7,review.getTitle());
			pstmt.setString(8,review.getContent());
			int count=pstmt.executeUpdate();
			return count;
		}finally{
			releaseConnection(con,pstmt);
			
		}
		
		
	}
	
	public int modifyReview(BoardReviewVO review)throws SQLException{
		Connection con=null;
		PreparedStatement pstmt=null;
		String insertQuery =" update boardreview set review_title=?,review_point=?,review_content=?";
		try{
			 con=getConnection();
			 pstmt=con.prepareStatement(insertQuery);
			 pstmt.setString(1, review.getTitle());
			 pstmt.setString(2, review.getPoint());
			 pstmt.setString(3, review.getContent());
			 
			int count=pstmt.executeUpdate();
			return count;
		}finally{
			releaseConnection(con, pstmt);
		}
		
		
	}
	
	public int deleteReview(int reviewNo) throws SQLException{
		Connection con =null;
		PreparedStatement pstmt= null;
		String deletQuery="delete from boardreview where review_no =?";
		try{
		  con=getConnection();
			pstmt=con.prepareStatement(deletQuery);
			pstmt.setInt(1, reviewNo);
			
			int count=pstmt.executeUpdate();
			return count;
		
		}finally{
			releaseConnection(con, pstmt);
		}
	}
	
	public BoardReviewVO findReviewByNo(int reviewNo) throws SQLException{
		
		Connection con =null;
		PreparedStatement pstmt=null;
		ResultSet rs= null;
		BoardReviewVO review=null;
		String findQuery="select review_no,m_no,m_name,review_hit,review_date," +
							"review_point,review_title,review_content" +
							" from boardreview where review_no=?";
		try{
			con = getConnection();
			pstmt =con.prepareStatement(findQuery);
			pstmt.setInt(1, reviewNo);
			rs=pstmt.executeQuery();
			if(rs.next()){
				review =new BoardReviewVO();
				review.setNo(rs.getInt("review_no"));
				review.setMemberNo(rs.getInt("m_no"));
				review.setName(rs.getString("m_name"));
				review.setHit(rs.getString("review_hit"));
				review.setDate(rs.getString("review_date"));
				review.setPoint(rs.getString("review_point"));
				review.setTitle(rs.getString("review_title"));
				review.setContent(rs.getString("review_content"));
			}
		}finally{
			releaseConnection(con, pstmt, rs);
		}
		
		return review;
	}
	
	public List<BoardReviewVO> findAllReview() throws SQLException{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String findAllReviewQuery="select review_no,m_no,m_name,review_hit," +
									"review_date,review_point,review_title,review_content" +
									 "from boardreivew";
		List reviewList = new ArrayList<BoardReviewVO>();
		
		try{
			con =getConnection();
			pstmt =con.prepareStatement(findAllReviewQuery);
			rs=pstmt.executeQuery();
			
			while(rs.next()){
				BoardReviewVO reivew = new BoardReviewVO(rs.getInt("review_no"),
														 rs.getInt("m_no"),
														 rs.getString("m_name"),
														 rs.getString("review_hit"),
														 rs.getString("review_date"),
														 rs.getString("review_point"),
														 rs.getString("review_title"),
														 rs.getString("review_content"));
				reviewList.add(reivew);
			}
			
		}finally{
			releaseConnection(con, pstmt, rs);
		}
		return reviewList;
	}
	
	public List<BoardReviewVO> findReviewByTitle(String reviewTitle)throws SQLException{
		Connection con =null;
		PreparedStatement pstmt=null;
		ResultSet rs =null;
		BoardReviewVO review =null;
		String findTitleQuery="select review_no,m_no,m_name,review_hit,review_date," +
							"review_point,review_title,review_content" +
							" from boardreview where review_title=?";
		
		List reviewList = new ArrayList<BoardReviewVO>();
		try{
			con = getConnection();
			pstmt =con.prepareStatement(findTitleQuery);
			pstmt.setString(1,reviewTitle);
			rs=pstmt.executeQuery();
			while(rs.next()){
				review =new BoardReviewVO();
				review.setNo(rs.getInt("review_no"));
				review.setMemberNo(rs.getInt("m_no"));
				review.setName(rs.getString("m_name"));
				review.setHit(rs.getString("review_hit"));
				review.setDate(rs.getString("review_date"));
				review.setPoint(rs.getString("review_point"));
				review.setTitle(rs.getString("review_title"));
				review.setContent(rs.getString("review_content"));
			}
		}finally{
			releaseConnection(con, pstmt, rs);
		}
		
		
		return reviewList;
	}
}
